import pymysql

class database:
    __db = ""
    __sql = ""
    __cursor = ""
    __data = ""

    def __init__(self, ip, username, password, database):
        self.__db = pymysql.connect(ip, username, password, database)
        self.__cursor = self.__db.cursor()
    def closeDataBase(self):
        self.__db.close()
############################################
    #得到列或值的逗号拼接
    def getcolumn(self, columns):
        result = ""
        lenth = len(columns)
        for i in range(lenth):
            result += str(columns[i])
            if i != (lenth - 1):
                result += ","
        print(result)
        return result;
    #执行sql语句
    def executesql(self):
        try:
            self.__cursor.execute(self.__sql)
            self.__db.commit()
            return True
        except:
            print("error")
            self.__db.rollback()
            return False

        # 执行带data的sql语句
    def executedata(self, data):
        try:
            self.__cursor.execute(self.__sql, data)
            self.__db.commit()
            return True
        except:
            print("error")
            self.__db.rollback()
            return False
    #获取执行sql语句后的数据
    def getdata(self):
        self.__data = self.__cursor.fetchall()
    #获取列命和值的拼接
    def col_val(self, col):
        result = ""
        lenth = len(col)
        for i in range(lenth):
            result += col[i]
            result += " = "
            result += "%s"
            if i != (lenth - 1):
                result += ","
        return result;
    #获取where条件
    def getcondition(self,conditions):
        result = ""
        lenth = len(conditions)
        for i in range(lenth):
            result += conditions[i]
            if i != (lenth - 1):
                result += " and "
        return result;
############################################
    #查询全部数据
    def selectall(self, tableName):
        self.__sql = """
            select * from %s
        """%(tableName)
        if (self.executesql() == False):
            return None
        self.getdata()
        return self.__data

    #查询部分数据
    def selectsome(self, tableName, tableColumn):
        self.__sql = """
            select %s from %s
        """%(self.getcolumn(tableColumn), tableName)
        if (self.executesql() == False):
            return None
        self.getdata()
        return self.__data

    #插入数据
    def insert(self, tableName, tableColumn, tableValue):
        self.__sql = "insert into " + tableName + "(" + self.getcolumn(tableColumn) + ")" + "values("
        shumu = len(tableColumn)
        for i in range(shumu):
            self.__sql += "%s"
            if (i != shumu - 1):
                self.__sql += ","
        self.__sql += ")"
        print(self.__sql)
        if (self.executedata(tableValue) == False):
            return None
        return 1

    #更新数据
    def update(self, tableName, column, value, condition):
        self.__sql = """
            update %s set %s where %s 
        """%(tableName, self.col_val(column), self.getcondition(condition))
        print(self.__sql)
        if (self.executedata(value) == False):
            return None
        return 1

    #删除数据
    def delete(self, tableName, condition):
        self.__sql = """
            delete from %s where %s
        """%(tableName, self.getcondition(condition))
        if (self.executesql() == False):
            return None
        return 1


